﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
//using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SerialPortDal
{
    public class SqliteHelper
    {
        private const string m_dbName = "spmDb.db";
        //  private static SQLiteConnection _conn = null;
        public SqliteHelper()
        {
            OpenSqlConnect();
        }

        private void OpenSqlConnect()
        {
            //连接数据库
            if (!File.Exists(m_dbName))
            {
                SQLiteConnection.CreateFile(m_dbName);
            }
            SQLiteConnection m_dbConnection = GetConnection();
            m_dbConnection.Open();
            SQLiteCommand m_command = new SQLiteCommand();
            m_command.Connection = m_dbConnection;
            m_command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='SEND_DATA_LOG'";
            if (0 == Convert.ToInt32(m_command.ExecuteScalar()))
            {
                //table - task does not exist.  
                InitDb();
            }
        }

        public static SQLiteConnection GetConnection()
        {
            //连接数据库

            /* if (_conn == null)
             {
                 if (!File.Exists(m_dbName))
                 {
                     SQLiteConnection.CreateFile(m_dbName);
                 }
                 DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
                 _conn = fact.CreateConnection();
                 _conn.ConnectionString = "Data Source="+ m_dbName;

             }*/

            //if (_conn == null)
            //{
            if (!File.Exists(m_dbName))
            {
                SQLiteConnection.CreateFile(m_dbName);
            }
            SQLiteConnection _conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr =
                new SQLiteConnectionStringBuilder();
            connstr.DataSource = m_dbName;
            _conn.ConnectionString = connstr.ToString();
            //}
            return _conn as SQLiteConnection;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseSqlConnect()
        {
            //if (_conn != null)
            //    _conn.Close();
        }
        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <returns></returns>
        public bool InitDb()
        {
            bool bRet = false;
            string sql = "CREATE TABLE send_data_log (" +
            "    send_data_log_id  INTEGER  PRIMARY KEY AUTOINCREMENT,           " +
            "    send_mode         INT      NOT NULL                             " +
            "                               DEFAULT ( 0 ),                       " +
            "    send_data_content TEXT     NOT NULL,                            " +
            "    create_dt         DATETIME DEFAULT ( CURRENT_TIMESTAMP ),       " +
            "    send_name         VARCHAR  UNIQUE                               " +
            "                               NOT NULL,                            " +
            "    send_times        INT      DEFAULT ( 0 )                        " +
            ")                                                                   ";
            int iRet = ExecuteNonQuery(sql);
            if (iRet == 0)
            {
                bRet = true;
            }
            return bRet;
        }

        /// <summary>
        /// ExecuteNonQuery操作，对数据库进行 增、删、改 操作
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <returns> </returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null);
        }

        /// <summary>
        /// ExecuteNonQuery操作，对数据库进行 增、删、改 操作（2）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <returns> </returns>
        public static int ExecuteNonQuery(string sql, CommandType commandType)
        {
            return ExecuteNonQuery(sql, commandType, null);
        }
        /// <summary>
        /// ExecuteNonQuery操作，对数据库进行 增、删、改 操作（3）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <param name="parameters">参数数组 </param>
        /// <returns> </returns>
        public static int ExecuteNonQuery(string sql, CommandType commandType, SQLiteParameter[] parameters)
        {
            int count = 0;
            using (SQLiteConnection connection = GetConnection())
            {
                connection.Open();
                SQLiteCommand m_command = new SQLiteCommand(sql);
                m_command.Connection = connection;
                m_command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SQLiteParameter parameter in parameters)
                    {
                        m_command.Parameters.Add(parameter);
                    }
                }
                count = m_command.ExecuteNonQuery();
            }
            return count;
        }
        /// <summary>
        /// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataSet类型结果（1）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <returns> </returns>
        public static DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, CommandType.Text, null);
        }
        /// <summary>
        /// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataSet类型结果（2）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <returns> </returns>
        public static DataSet ExecuteDataSet(string sql, CommandType commandType)
        {
            return ExecuteDataSet(sql, commandType, null);
        }
        /// <summary>
        /// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataSet类型结果（3）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <param name="parameters">参数数组 </param>
        /// <returns> </returns>
        public static DataSet ExecuteDataSet(string sql, CommandType commandType, SQLiteParameter[] parameters)
        {
            DataSet ds = new DataSet();
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SQLiteParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    adapter.Fill(ds);
                }
            }
            return ds;
        }
        /// <summary>
        /// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataTable类型结果（1）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <returns> </returns>
        public static DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, CommandType.Text, null);
        }
        /// <summary>
        /// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataTable类型结果（2）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <returns> </returns>
        public static DataTable ExecuteDataTable(string sql, CommandType commandType)
        {
            return ExecuteDataTable(sql, commandType, null);
        }
        /// <summary>
        /// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataTable类型结果（3）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <param name="parameters">参数数组 </param>
        /// <returns> </returns>
        public static DataTable ExecuteDataTable(string sql, CommandType commandType, SQLiteParameter[] parameters)
        {
            DataTable data = new DataTable();
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SQLiteParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            return data;
        }
        /// <summary>
        /// ExecuteReader执行一查询，返回一SqlDataReader对象实例（1）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <returns> </returns>
        public static SQLiteDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, CommandType.Text, null);
        }
        /// <summary>
        /// ExecuteReader执行一查询，返回一SqlDataReader对象实例（2）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <returns> </returns>
        public static SQLiteDataReader ExecuteReader(string sql, CommandType commandType)
        {
            return ExecuteReader(sql, commandType, null);
        }
        /// <summary>
        /// ExecuteReader执行一查询，返回一SqlDataReader对象实例（3）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <param name="parameters">参数数组 </param>
        /// <returns> </returns>
        public static SQLiteDataReader ExecuteReader(string sql, CommandType commandType, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = GetConnection())
            {
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SQLiteParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                connection.Open();
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        /// <summary>
        /// ExecuteScalar执行一查询，返回查询结果的第一行第一列（1）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <returns> </returns>
        public static Object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, null);
        }
        /// <summary>
        /// ExecuteScalar执行一查询，返回查询结果的第一行第一列（2）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <returns> </returns>
        public static Object ExecuteScalar(string sql, CommandType commandType)
        {
            return ExecuteScalar(sql, commandType, null);
        }
        /// <summary>
        /// ExecuteScalar执行一查询，返回查询结果的第一行第一列（3）
        /// </summary>
        /// <param name="sql">要执行的SQL语句 </param>
        /// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
        /// <returns> </returns>
        public static Object ExecuteScalar(string sql, CommandType commandType, SQLiteParameter[] parameters)
        {
            object result = null;
            using (SQLiteConnection connection = GetConnection())
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SQLiteParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    connection.Open();
                    result = command.ExecuteScalar();
                }
            }
            return result;
        }
        /// <summary>
        /// 返回当前连接的数据库中所有由用户创建的数据库
        /// </summary>
        /// <returns> </returns>
        public static DataTable GetTables()
        {
            DataTable data = null;
            using (SQLiteConnection connection = GetConnection())
            {
                connection.Open();
                data = connection.GetSchema("Tables");
            }
            return data;
        }
    }
}
